"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : Compatibility
Case Name   : 非数值类型导入
Create At   : 2023/07/19
Owner       : n@ningyali
Description :
        1.创建表，指定列类型为非数值类型
        2.插入数据
        3.使用copy导出数据，生成文件
        4.load data使用第3步生成文件导入数据
        5.查询表数据
        6.清理数据库，删除数据文件
Expect      :
        1.成功
        2.成功
        3.成功
        4.成功
        5.成功
        6.成功
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class LoadData0091(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.constant = Constant()
        self.load_data = LoadData()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.case_lable = 'load_data_0091'
        self.db_name = f'db_{self.case_lable}'
        self.t_name = f't_{self.case_lable}'
        self.type_name = f'type_{self.case_lable}'
        self.f_path = os.path.join('/home', self.pri_user.ssh_user,
                                   f'{self.case_lable}.txt')
        self.cols = '''col1 money, --货币类型
            col2 boolean, --布尔型
            col3 CHAR(4), --字符类型
            col4 CHARACTER(4), 
            col5 NCHAR(4),
            col6 VARCHAR(10),
            col7 CHARACTER VARYING(10),
            col8 VARCHAR2(10),
            col9 NVARCHAR2(10),
            col10 TEXT,
            col11 CLOB,
            col12 BLOB, --二进制类型
            col13 RAW,
            col14 BYTEA,
            col15 DATE, --时间日期类型
            col16 TIME,
            col17 time with time zone,
            col18 timestamp,
            col19 timestamp with time zone,
            col20 SMALLDATETIME,
            col21 abstime,
            col22 cidr, --网络地址类型
            col23 inet,
            col24 macaddr,
            col25 BIT(3), --位串类型
            col26 BIT VARYING(5),
            col27 UUID, --UUID类型
            col28 tsvector, --文本搜索类型
            col29 tsquery,
            col30 json, --json类型
            col31 jsonb,
            set hll(14), --HLL类型
            col33 int4range, --范围类型
            col34 int8range,
            col35 numrange,
            col36 tsrange,
            col37 tstzrange,
            col38 daterange,
            col39 HASH16, --账本数据库HASH类型
            col40 HASH32'''
        self.insert_sql = f"insert into {self.t_name} values (" \
            f"'52093.89'::money, " \
            f"true, 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', " \
            f"'aa', empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF', " \
            f"'1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', " \
            f"'1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', " \
            f"'1900-01-01 00:00:00', '1900-01-01 00:00:00', " \
            f"'1910-01-01 00:00:00', '10.10.10.10', '10.10.10.10', " \
            f"'08002b010203', B'10'::bit(3), B'00', " \
            f"'a0eebc999c0b4ef8bb6d6bb9bd380a11', 'test', 'test', " \
            f"'{{{{\\\"aa\\\":1}}}}'::json, '{{{{\\\"bb\\\":2}}}}'::jsonb, " \
            f"hll_empty(14,-1), '[1,10]', '[1,10]', '[0.00, 10.00]', " \
            f"'[1900-01-01 00:00:00, 2020-01-01 00:00:00]', " \
            f"'[1900-01-01 00:00:00 pst, 2020-01-01 00:00:00 pst]', " \
            f"'(1900-01-01, 2020-01-01)','ffff', " \
            f"'ffffffffffffffffffffffffffffffff');"

        text = '-----prestep1:创建兼容B库；expect:创建成功-----'
        self.log.info(text)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text)

        text = '----prestep2:配置enable_copy_server_files参数值为on，' \
               '允许使用copy to/from expect: 成功----'
        self.log.info(text)
        befor_val = self.pri_sh.execute_gsguc('check', '',
                                              'enable_copy_server_files',
                                              get_detail=True)
        self.log.info(befor_val)
        guc_res = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'enable_copy_server_files=on')
        self.assertTrue(guc_res)

    def test_load(self):
        text = '-----step1：创建表，指定列类型为非数值类型；expect：建表成功-----'
        self.log.info(text)
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name, self.cols)
        self.assertTrue(res, '执行失败:' + text)

        text = '-----step2：插入数据；expect：成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(self.insert_sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('INSERT 0 1', res, '执行失败:' + text)

        text = '-----step3：使用copy导出数据；expect：成功-----'
        self.log.info(text)
        sql = f"copy (select * from t_load_data_0091) to '{self.f_path}';"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('COPY 1', res, '执行失败:' + text)

        text = '-----step4：导入数据；expect：成功-----'
        self.log.info(text)
        sql = f"truncate table {self.t_name};" \
            f"load data infile '{self.f_path}' into table {self.t_name};"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('COPY 1', res, '执行失败' + text)

        text = '-----step5：查询表数据；expect：成功-----'
        self.log.info(text)
        sql = f"set bytea_output=escape;" \
              f"select * from {self.t_name};"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        rows, _ = self.common.parse_sql_query_res(res)
        expect = [
            ['$52,093.89', 't', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa',
             'aa', 'aa', '', 'DEADBEEF', 'jo\\000se', '1900-01-01',
             '00:00:00', '00:00:00+08:05:43', '1900-01-01 00:00:00+08:05:43',
             '1900-01-01 00:00:00+08:05:43', '1900-01-01 00:00:00',
             '1910-01-01 00:00:00+08', '10.10.10.10/32', '10.10.10.10',
             '08:00:2b:01:02:03', '010', '00',
             'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', "'test'", "'test'",
             '{"aa":1}', '{"bb": 2}',
             'HLL\\000\\000\\000\\000\\000+\\005\\000\\000\\000\\000\\000'
             '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'
             '\\000\\000',
             '[1,11)', '[1,11)', '[0.00,10.00]',
             '["1900-01-01 00:00:00","2020-01-01 00:00:00"]',
             '["1900-01-01 00:00:00+08:05:43","2020-01-01 00:00:00+08"]',
             '[1900-01-02,2020-01-01)', '000000000000ffff',
             'ffffffffffffffffffffffffffffffff']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        s6_text = '----step6: 还原enable_copy_server_files参数值为off ' \
                  'expect: 成功----'
        self.log.info(s6_text)
        guc_res = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'enable_copy_server_files=off')
        after_val = self.pri_sh.execute_gsguc('check', '',
                                              'enable_copy_server_files',
                                              get_detail=True)
        self.log.info(after_val)

        self.log.info('-----step7：清理环境；expect：成功-----')
        res = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                       self.db_name,
                                       self.f_path)
        self.assertTrue(res, '执行失败:清理环境')
        self.assertTrue(guc_res, f'执行失败: {s6_text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
